package com.temp.check.base;


import java.io.Closeable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class DBUtil {


    /**
     * 数据库连接配置
     *
     * @param ip
     * @param root
     * @param pswd
     * @return
     */
    public static DBConfig getConfig(String ip, String root, String pswd) {
        StringBuilder s = new StringBuilder();
        s.append("jdbc:mysql://" + ip + "/imsp_order_logistics");
        s.append("?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&tinyInt1isBit=false&allowMultiQueries=true&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true");
        DBConfig config = new DBConfig().setUrl(s.toString())
                .setDriver("com.mysql.cj.jdbc.Driver")
                .setUsername(root).setPassword(pswd);
        return config;
    }

    /**
     * 取得连接
     *
     * @return
     * @throws Exception
     */
    private static Connection getConn(DBConfig config) throws Exception {
        // 加载驱动
        Class.forName(config.getDriver());
        // 建立连接
        return DriverManager.getConnection(config.getUrl(), config.getUsername(), config.getPassword());
    }


    /**
     * 所有数据列
     *
     * @return
     * @throws Exception
     */
    public static List<DBColumnVO> queryColumn(DBConfig config, DBCond cond) throws Exception {
        Statement st = null;
        ResultSet rs = null;
        Connection conn = null;
        try {
            //取得连接
            conn = getConn(config);
            // 创建执行对象
            st = conn.createStatement();
            // 执行查询语句
            StringBuilder dateColumnSql = new StringBuilder();
            dateColumnSql.append(" SELECT ");
            dateColumnSql.append("     c.TABLE_SCHEMA         as dbName ");
            dateColumnSql.append("     ,c.TABLE_NAME          as tableName ");
            dateColumnSql.append("     ,t.TABLE_COMMENT       as tableComment ");
            dateColumnSql.append("     ,c.COLUMN_NAME         as columnName ");
            dateColumnSql.append("     ,c.COLUMN_TYPE         as columnType");
            dateColumnSql.append("     ,c.COLUMN_COMMENT      as columnComment ");
            dateColumnSql.append("   FROM information_schema.`COLUMNS` c,information_schema.TABLES t  ");
            dateColumnSql.append("  WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME ");
            dateColumnSql.append("    AND c.TABLE_SCHEMA not in ('information_schema','performance_schema')");
            if (cond.getDbLike() != null && cond.getDbLike().length() > 0) {
                dateColumnSql.append("    AND c.TABLE_SCHEMA  like '%" + cond.getDbLike() + "%'");
            }
            if (cond.getTableLike() != null && cond.getTableLike().length() > 0) {
                dateColumnSql.append("    AND c.TABLE_SCHEMA  like '%" + cond.getTableLike() + "%'");
            }
            if (cond.getColumnLike() != null && cond.getColumnLike().length() > 0) {
                dateColumnSql.append("    AND LOWER(c.COLUMN_NAME)  like '%" + cond.getColumnLike().toLowerCase() + "%'");
            }
            if (cond.getColumnLikeList() != null && cond.getColumnLikeList().size() > 0) {
                dateColumnSql.append("    AND (");
                for (int i = 0; i < cond.getColumnLikeList().size(); i++) {
                    if (i > 0) {
                        dateColumnSql.append(" or ");
                    }
                    dateColumnSql.append("  LOWER(c.COLUMN_NAME)  like '%" + cond.getColumnLikeList().get(i).toLowerCase() + "%'");
                }

                dateColumnSql.append("  )");
            }
            dateColumnSql.append("     order by c.TABLE_SCHEMA,c.TABLE_NAME,c.COLUMN_NAME");
            rs = st.executeQuery(dateColumnSql.toString());
            // 处理结果
            List<DBColumnVO> dataList = new ArrayList<>();
            DBColumnVO vo;
            while (rs.next()) {
                vo = new DBColumnVO();
                vo.setDbName(rs.getString("dbName"));
                vo.setTableName(rs.getString("tableName"));
                vo.setTableComment(rs.getString("tableComment"));
                vo.setColumnName(rs.getString("columnName").toLowerCase());
                vo.setColumnType(rs.getString("columnType").toLowerCase());
                vo.setColumnComment(rs.getString("columnComment").toLowerCase());
                dataList.add(vo);
            }
            return dataList;
        } finally {
            close(conn, rs, st);
        }
    }

    /**
     * 关闭
     *
     * @param objects
     */
    private static void close(Object... objects) {
        if (objects != null) {
            for (Object object : objects) {
                if (object instanceof Closeable) {
                    try {
                        ((Closeable) object).close();
                    } catch (Exception ex) {

                    }
                }
            }
        }
    }


}
